CREATE OR REPLACE PACKAGE BODY PKG_BRN_MANAGER AS

/*****************************************************************************
* Name : PKG_BRN_MANAGER 
* Type : Package body
* Description : Package to facilitate the following responsibilities : -
*                1. Get the BRN Number from the table T_BASKET_REFERENCE_MASTER
*                2. Return BRN and expiry timestamp.
*
*
* Amendment History :
*
* Version  Date         Changed By     Reason (e.g. Initial, Change Ref)
******************************************************************************
* 0.1      28/08/2013    Rajeeva Tiwari      Initial
*****************************************************************************/

PROCEDURE get_brn( p_ap_txn_id IN t_basket_reference.ap_txn_id%TYPE
                 , p_reconciliation_cycle_id IN t_basket_reference.reconciliation_cycle_id%TYPE
                 , p_reconciliation_date IN t_basket_reference.reconciliation_date%TYPE
                 , p_brn  OUT t_basket_reference_master.basket_reference_number%TYPE
                 , p_exp_tmstmp  OUT t_basket_reference.expiry_tmstmp%TYPE) 
IS PRAGMA AUTONOMOUS_TRANSACTION;
/*****************************************************************************
* Name : get_brn 
* Type : procedure 
* Description : Package to facilitate the following responsibilities : -
*                1. Get the BRN Number from the table T_BASKET_REFERENCE_MASTER
*                2. Return BRN and expiry timestamp.
*
* Parameters In :   p_ap_txn_id - AP transaction id
*                   p_reconciliation_cyle_id - reconciliation cycle id
*                   p_reconciliation_date - reconciliation date
* Parameters Out :  p_brn - Basket reference number
*                   p_exp_tmstmp - expiry timestamp of basket reference number
*
* Return : NONE 
*
* Amendment History :
*
* Version  Date         Changed By     Reason (e.g. Initial, Change Ref)
******************************************************************************
* 0.1      28/08/2013    Rajeeva Tiwari      Initial
*****************************************************************************/
v_procedure_name           VARCHAR2(30 CHAR)  := 'get_brn';
v_brn_id NUMBER(6) := 0;
v_brn_expiry_offset NUMBER(6);
e_nobrn            EXCEPTION;
e_noparameter      EXCEPTION;
BEGIN
          
   SELECT basket_reference_number_id_seq.NEXTVAL
   INTO   v_brn_id
   FROM   DUAL;
   BEGIN
      SELECT basket_reference_number 
      INTO  p_brn
      FROM t_basket_reference_master
      WHERE basket_reference_number_id = v_brn_id
      AND   availability_flag = 1;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         ROLLBACK;      
         RAISE e_nobrn;   
      WHEN others THEN
         ROLLBACK;
         RAISE;
   END;
   
   UPDATE t_basket_reference_master
   SET    availability_flag = 0
     ,    last_refresh_tmstmp = current_timestamp
   WHERE basket_reference_number_id = v_brn_id;
   
   BEGIN
      SELECT TO_NUMBER(parameter_value)
           , current_timestamp + numtodsinterval( parameter_value, 'second' )
      INTO   v_brn_expiry_offset
          ,  p_exp_tmstmp 
      FROM t_parameter
      WHERE parameter_name = 'BRN_EXPIRY_OFFSET';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         ROLLBACK;      
         RAISE e_noparameter;   
      WHEN others THEN
         ROLLBACK;
         RAISE;
   END;
          
   INSERT INTO t_basket_reference
   (   basket_reference_id
   ,   ap_txn_id
   ,   basket_reference_number
   ,   reconciliation_cycle_id
   ,   reconciliation_date
   ,   expiry_tmstmp
   )
   VALUES
   (   basket_reference_id_seq.nextval
   ,   p_ap_txn_id
   ,   p_brn
   ,   p_reconciliation_cycle_id
   ,   p_reconciliation_date
   ,   p_exp_tmstmp
   );
 
   COMMIT;   
EXCEPTION
   WHEN e_nobrn THEN
      ROLLBACK;      
         pkg_logger.log_error( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_app_error_message => 'Error occured during BRN allocation. BRN Not available '
                             , p_oracle_message    => dbms_utility.format_error_stack || dbms_utility.format_error_backtrace
                             );
         pkg_logger.log_progress( p_package_name      => gc_package_name
                                , p_procedure_name    => v_procedure_name
                                , p_progress_message  => 'EXCEPTION: Error while allocation BRN  '||p_brn
                                , p_stage => 'BRN allocation'
                                , p_work_identifier => p_brn                                );

         RAISE_APPLICATION_ERROR (-20001, 'Exception occured in ' || gc_package_name ||'.'||v_procedure_name ||dbms_utility.format_error_stack || dbms_utility.format_error_backtrace);

   WHEN e_noparameter THEN
      ROLLBACK;      
         pkg_logger.log_error( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_app_error_message => 'Error occured during BRN allocation. Parameter not set available '
                             , p_oracle_message    => dbms_utility.format_error_stack || dbms_utility.format_error_backtrace
                             );
         pkg_logger.log_progress( p_package_name      => gc_package_name
                                , p_procedure_name    => v_procedure_name
                                , p_progress_message  => 'EXCEPTION: Error while allocation BRN  '||p_brn
                                , p_stage => 'BRN allocation'
                                , p_work_identifier => p_brn                                );

         RAISE_APPLICATION_ERROR (-20002, 'Exception occured in ' || gc_package_name ||'.'||v_procedure_name ||dbms_utility.format_error_stack || dbms_utility.format_error_backtrace);


   WHEN others THEN
      ROLLBACK;      
         pkg_logger.log_error( p_package_name      => gc_package_name
                             , p_procedure_name    => v_procedure_name
                             , p_app_error_message => 'Error occured during BRN allocation.'
                             , p_oracle_message    => dbms_utility.format_error_stack || dbms_utility.format_error_backtrace
                             );
         pkg_logger.log_progress( p_package_name      => gc_package_name
                                , p_procedure_name    => v_procedure_name
                                , p_progress_message  => 'EXCEPTION: Error while allocation BRN  '||p_brn
                                , p_stage => 'BRN allocation'
                                , p_work_identifier => p_brn
                                );

         RAISE_APPLICATION_ERROR (-20003, 'Exception occured in ' || gc_package_name ||'.'||v_procedure_name ||dbms_utility.format_error_stack || dbms_utility.format_error_backtrace);
END get_brn;

END PKG_BRN_MANAGER;
/
